package aggregate.aggregator;

import java.rmi.NotBoundException;
import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import aggregate.aggregator.remote.Category;
import aggregate.aggregator.remote.Client;
import aggregate.aggregator.remote.Comment;
import aggregate.aggregator.remote.ContentItem;

public class Database {
	
	public Database() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public Connection connect() {
		try {
			return DriverManager.getConnection ("jdbc:mysql:///aggregate", "root", "");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public List<Category> getCategories(Connection connection) {
		Statement stmt = null;
		try {
			stmt = connection.createStatement();
			String query = "select id, name from category";
			stmt.executeQuery(query);
			ResultSet results = stmt.getResultSet();
			
			ArrayList<Category> categories = new ArrayList<Category>();
			while(results.next()) {
				Category category = createCategory(results);
				categories.add(category);
			}
			return categories;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return new ArrayList<Category>();
	}

	public Category getCategoryById(Connection connection, int categoryId) {
		PreparedStatement stmt = null;
		try {
			stmt = connection.prepareStatement("select id, name from category where id = ?");
			stmt.setInt(1, categoryId);
			ResultSet result = stmt.executeQuery();
			result.next();
			Category category = createCategory(result);
			return category;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return null;
	}
	
	private Category createCategory(ResultSet resultSet) throws SQLException {
		Category category = new Category(resultSet.getString("name"));
		category.setId(resultSet.getInt("id"));
		return category;
	}

	public void subscribeClient(Connection connection, String hostname, int categoryId) {
		PreparedStatement stmt = null;
		try {
			stmt = connection.prepareStatement("insert into clients (hostname, catId) " +
					"values (?, ?)");
			stmt.setString(1, hostname);
			stmt.setInt(2, categoryId);
			stmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	public List<Client> getClientsForCategory(Connection connection, int catId) {
		PreparedStatement stmt = null;
		try {
			stmt = connection.prepareStatement("select hostname from clients" +
					" where catId = ?");
			stmt.setInt(1, catId);
			ResultSet results = stmt.executeQuery();
			
			ArrayList<Client> clients = new ArrayList<Client>();
			while(results.next()) {
				Client client;
				try {
					client = Client.connectToClient(results.getString("hostname"));
					clients.add(client);
				} catch (RemoteException e) {
					e.printStackTrace();
				} catch (NotBoundException e) {
					e.printStackTrace();
				}
			}
			return clients;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return new ArrayList<Client>();
	}

	public List<Feed> getFeedsForCategory(Connection connection, int catId) {
		PreparedStatement stmt = null;
		try {
			stmt = connection.prepareStatement("select id, url, lastNewsTitle from feeds" +
					" where catId = ?");
			stmt.setInt(1, catId);
			ResultSet results = stmt.executeQuery();
			
			ArrayList<Feed> feeds = new ArrayList<Feed>();
			while(results.next()) {
				Feed feed = createFeed(results);
				feeds.add(feed);
			}
			return feeds;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return new ArrayList<Feed>();
	}

	private Feed createFeed(ResultSet results) throws SQLException {
		Feed feed = new Feed(results.getString("url"));
		feed.setId(results.getInt("id"));
		feed.setLastNewsTitle(results.getString("lastNewsTitle"));
		return feed;
	}

	public void insertContent(Connection con, ContentItem item) {
		PreparedStatement stmt = null;
		try {
			stmt = con.prepareStatement("insert into content (title, body, url, created, catId)"
				+ " values (?, ?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS);
			stmt.setString(1, item.getTitle());
			stmt.setString(2, item.getBody());
			stmt.setString(3, item.getURL());
			stmt.setLong(4, item.getCreated());
			stmt.setInt(5, item.getCategoryId());
			stmt.execute();
			ResultSet resultKeys = stmt.getGeneratedKeys();
			resultKeys.next();
			item.setId(resultKeys.getInt(1));
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	public void updateLastNewsForFeed(Connection connection, Feed feed) {
		PreparedStatement stmt = null;
		try {
			stmt = connection.prepareStatement("update feeds set lastNewsTitle=?" +
					" where id = ?");
			stmt.setString(1, feed.getLastNewsTitle());
			stmt.setInt(2, feed.getId());
			stmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	public void insertRatingForContent(Connection connection, int contentId, double value) {
		PreparedStatement stmt = null;
		try {
			stmt = connection.prepareStatement("insert into rating (value, contentId) values (?, ?)");
			stmt.setDouble(1, value);
			stmt.setInt(2, contentId);
			stmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	public int getCategoryIdForContentId(Connection connection, int contentId) {
		PreparedStatement stmt = null;
		try {
			stmt = connection.prepareStatement("select catId from content where id = ?");
			stmt.setInt(1, contentId);
			ResultSet result = stmt.executeQuery();
			result.next();
			return result.getInt("catId");
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return -1;
	}

	public void insertComment(Connection connection, Comment comment) {
		PreparedStatement stmt = null;
		try {
			stmt = connection.prepareStatement("insert into comment (contentId, subject, body, author, created)" +
					" values (?, ?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS);
			stmt.setInt(1, comment.getContentId());
			stmt.setString(2, comment.getSubject());
			stmt.setString(3, comment.getBody());
			stmt.setString(4, comment.getAuthor());
			stmt.setTimestamp(5, comment.getCreated());
			stmt.execute();
			
			ResultSet resultKeys = stmt.getGeneratedKeys();
			resultKeys.next();
			comment.setId(resultKeys.getInt(1));
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
}
